﻿
using Castle.Core.Internal;
using Coldairarrow.Business.Interface;
using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.Interface;
using Coldairarrow.Util;
using Dynamitey;
using Microsoft.VisualBasic;
using Newtonsoft.Json.Linq;
using OfficeOpenXml.Drawing.Slicer.Style;
using org.apache.zookeeper.data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics.Tracing;
using System.Drawing;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Security.Cryptography;
using System.Threading;
using System.Threading.Tasks;
using static AutoMapper.Internal.CollectionMapperExpressionFactory;
using static Coldairarrow.Business.Interface.EC_ReqDTO_SyncOrder.Biz_Content;
using static Coldairarrow.Entity.Interface.YDOrdersDTO.Orders.Delivery;
using static Coldairarrow.Entity.Interface.YDReturnsDTO.Returns;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace Coldairarrow.Business.Interface
{
    public class OL_Rep_StorageFeeInterfaceBusiness : Interface_BaseBusiness, IJob_Business, ITransientDependency
    {
        public List<OL_FilterDTO> Filters;
        public OL_Rep_StorageFeeInterfaceBusiness()
        {
            this.InterfaceName = "同步海外仓储费";
            //获取字段的映射关系
            tableField = GetMapField("editRow");
            //索引字段
            Keys = GetKeys("editRow");
            indexFields.Add(Keys.Where(X => X.Type == "K").First().Field);
            //查询过滤条件
            Filters = new List<OL_FilterDTO>();


            //查询执行前的事件
            BeforeQueryEvent += InterfaceBusiness_BeforeQueryEvent;
            //查询执行中的事件
            OnQueryEvent += InterfaceBusiness_OnQueryEvent;
            //执行非查询请求中的事件
            OnNoQueryEvent += InterfaceBusiness_OnNoQueryEvent;

        }

        /// <summary>
        /// 执行非查询请求中时间
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="noQueryEventArgs"></param>
        private void InterfaceBusiness_OnNoQueryEvent(object sender, NoQueryEventArgs noQueryEventArgs)
        {
            //API请求是否成功
            noQueryEventArgs.Success = noQueryEventArgs.Result["success"].ToString().ToBool();
        }

        /// <summary>
        /// 查询执行前的事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="queryEventArgs"></param>
        private void InterfaceBusiness_BeforeQueryEvent(object sender, QueryEventArgs queryEventArgs)
        {
            //执行查询前添加过滤条件
            if (queryEventArgs.Page.Filters.IsNullOrEmpty() && !Filters.IsNullOrEmpty())
            {
                queryEventArgs.Page.Filters = Filters;
            }

        }

        /// <summary>
        /// 查询执行中的事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="queryEventArgs"></param>
        private void InterfaceBusiness_OnQueryEvent(object sender, QueryEventArgs queryEventArgs)
        {
            //是否查询完成
            if (queryEventArgs.Result["data"]["rows"].ToList().Count == 0)
            {
                queryEventArgs.Page.Complete = true;
            }
            //向总行中添加新的行
            queryEventArgs.Rows.AddRange(queryEventArgs.Result["data"]["rows"].ToList());
            //获取总页数
            queryEventArgs.Page.Total = queryEventArgs.Result["data"]["total"].ToString().ToInt();
            //获取查询成功标志
            queryEventArgs.Success = queryEventArgs.Result["success"].ToString().ToBool();
        }


        /// <summary>
        /// 执行增删改查业务
        /// </summary>
        public async void ExecuteInterface()
        {

            try
            {
                //数据库连接
                ConString = JsonSettingHelper.GetSetting("Database:BaseDb:ConnectionString");
                SqlDb = (SqlServerHelper)DbHelperFactory.GetDbHelper(EFCore.Sharding.DatabaseType.SqlServer, ConString);

                //获取此接口的所有API信息
                this.interfaceOption = SqlDb.GetListBySql<Base_InterfaceOption>(@$"SELECT * FROM  Base_InterfaceOption(NOLOCK) WHERE Name='{InterfaceName}' ");
                Base_InterfaceOption queryOption = this.interfaceOption.Where(o => o.OperateType == "getFilterRows").First();//查询API配置
                Base_InterfaceOption insertOption = this.interfaceOption.Where(o => o.OperateType == "addRow").First();//新增API配置
                Base_InterfaceOption upDateOption = this.interfaceOption.Where(o => o.OperateType == "editRow").First();//更行API配置
                Base_InterfaceOption deleteOption = this.interfaceOption.Where(o => o.OperateType == "deleteRow").First();//删除API配置
                //获取开始日期
                string dateForm = DateTime.Now.AddMonths(-queryOption.ExcInterval).ToString("yyyy-MM") + "-01";
                //获取汇率
                this.SqlDb._conStrings[0] = JsonSettingHelper.GetSetting("Database:BaseDb:K3ConnectionString");
                List<View_Rate> rateList = SqlDb.GetListBySql<View_Rate>(@$"SELECT * FROM View_Rate(NOLOCK) WHERE 生效时间>='{dateForm}'");

                //获取店铺
                OL_ShopInterfaceBusiness OL_Shop = new OL_ShopInterfaceBusiness();
                OL_Shop.Filters.Add(
            new OL_FilterDTO() { ControlId = "", DataType = 29, SpliceType = 1, FilterType = 8, Value = "" }
                //new OL_FilterDTO() { ControlId = "661d1c34387bc49b7a55d06e", DataType = 15, SpliceType = 1, FilterType = 14, Value = "2024-11-24" }
                );
                List<OL_Shop> shopList = OL_Shop.Query<OL_Shop>(OL_Shop.queryOption, OL_Shop.tableField, OL_Shop.SqlDb);
                shopList.ForEach(x =>
                {
                    //店铺
                    List<AssTable> table = x.Platform_.ToObject<List<AssTable>>();
                    if (!table.IsNullOrEmpty() && table.Count > 0) x.Platform_ = table.IsNullOrEmpty() ? "" : table.First().sid;
                });


                List<OL_Rep_StorageFee> feeList = new List<OL_Rep_StorageFee>();

                //获取亚马逊数据
                var list = GetAMZData(dateForm, rateList, shopList);
                feeList.AddRange(list);
                //获取九方仓储费
                var list1 = GetJFData(dateForm, rateList, shopList);
                feeList.AddRange(list1);
                //获取橙联仓储费
                var list2 = GetCLData(dateForm, rateList, shopList);
                feeList.AddRange(list2);
                //获取众包仓储费
                var list3 = GetZBData(dateForm, rateList, shopList);
                feeList.AddRange(list3);
                //获取BGA仓储费
                var list4 = GetBGAData(dateForm, rateList, shopList);
                feeList.AddRange(list4);




                List<OL_Rep_StorageFee> sourceData = new List<OL_Rep_StorageFee>();
                sourceData = feeList;






                //获取目标数据
                Filters.Clear();
                Filters.Add(
                //13:>,14>=15:<,16<=
                new OL_FilterDTO() { ControlId = "686f8240a31d84c7f6a45354", DataType = 15, SpliceType = 1, FilterType = 14, Value = dateForm }//FDATE字段过滤日开
            );
                List<OL_Rep_StorageFee> targetData = await QueryAsync<OL_Rep_StorageFee>(queryOption, tableField, SqlDb);
                if (this.State > 0 && sourceData.Count > 0)
                {
                    //更新
                    #region
                    List<OL_Rep_StorageFee> updataItem = sourceData.Intersect(targetData, new ComparerFromKeys<OL_Rep_StorageFee>(Keys, "RowId")).ToList();//获取交集(更新项)
                    List<OL_RowDTO> updataData = ConvertOL_Row(updataItem, tableField);//需要更新的数据集合
                    ExecuteNoQuery<OL_RowDTO>(updataData, upDateOption, SqlDb);//执行更新
                    #endregion

                    //新增
                    #region
                    List<OL_Rep_StorageFee> insertItem = sourceData.Except(targetData, new ComparerFromKey<OL_Rep_StorageFee>(indexFields.First(), "RowId")).ToList();//获取源数据对目标数据的差集(新增项)
                    List<OL_RowDTO> insertData = ConvertOL_Row(insertItem, tableField);//需要新增的数据集合
                    ExecuteNoQuery<OL_RowDTO>(insertData, insertOption, SqlDb);//执行插入行
                    #endregion

                    //删除
                    #region
                    List<OL_Rep_StorageFee> deleteItem = targetData.Except(sourceData, new ComparerFromKey<OL_Rep_StorageFee>(indexFields.First(), "RowId")).ToList();//获取目标数对据源数据的差集(删除项)
                    List<OL_Rep_StorageFee> repeatItem = targetData.GroupBy(x => x.GetPropertyValue(indexFields.First())).Where(x => x.Count() > 1).Select(x => x.FirstOrDefault()).ToList();
                    //将重复的项也一起加入删除的列表
                    deleteItem.AddRange(repeatItem);
                    //筛选出删除列表中重复的项
                    var temp = deleteItem.GroupBy(x => x.GetPropertyValue(indexFields.First())).Where(x => x.Count() > 1).Select(x => x.FirstOrDefault()).ToList();
                    deleteItem.AddRange(temp);
                    List<OL_RowDTO> deleteData = ConvertOL_Row(deleteItem, tableField);//需要删除的数据集合
                    ExecuteNoQuery<OL_RowDTO>(deleteData, deleteOption, SqlDb);//执行删除行
                    #endregion
                }
            }


            catch (Exception ex)
            {
                AddLog(SqlDb, ex.Message, InterfaceName, "erro", "");
            }
        }




        /// <summary>
        /// 将源数据根据字典映射关系转换成OL_RowDTO
        /// </summary>
        /// <typeparam name="T">源数据</typeparam>
        /// <param name="sourceData">源数据</param>
        /// <param name="map">Key为目标字段名称，Value为源字段名称</param>
        /// <returns></returns>
        public List<OL_RowDTO> ConvertOL_Row<T>(List<T> sourceData, Dictionary<string, string> map)
        {
            List<OL_RowDTO> list = new List<OL_RowDTO>();
            object rowId;
            foreach (var item in sourceData)
            {
                rowId = item.GetPropertyValue("RowId");
                //需要新增的数据
                OL_RowDTO row = new OL_RowDTO() { RowId = rowId.IsNullOrEmpty() ? null : rowId.ToString(), Controls = new List<Control>() };
                foreach (var c in map)
                {
                    //获取字段的键值做判断
                    var field = Keys.Where(x => x.TField == c.Key).ToList();
                    string isKey = "";
                    if (field.Count > 0) isKey = field.First().Type;
                    //判断对象的字段是否存在
                    if (item.ContainsProperty(c.Value) && isKey != "N")
                    {
                        //存在则添加数据
                        row.Controls.Add(new Control() { ControlId = c.Key, Value = item.GetPropertyValue(c.Value) });
                    }
                }
                list.Add(row);
            }
            return list;
        }

        public List<OL_Rep_StorageFee> GetAMZData(string dateForm, List<View_Rate> rateList, List<OL_Shop> shopList)
        {
            //数据库连接
            this.SqlDb._conStrings[0] = JsonSettingHelper.GetSetting("Database:BaseDb:ConnectionString");
            //获取亚马逊月仓费
            List<StorageFeeMonth> storageFeeMonthList = this.SqlDb.GetListBySql<StorageFeeMonth>(@$"SELECT [sid],month_of_charge,estimated_monthly_storage_fee,currency FROM SD_LX_StorageFeeMonth WHERE  CONVERT(DATE,(month_of_charge+'-01'))>='{dateForm}'");
            //获取亚马逊长期仓储费
            List<StorageFeeLong> storageFeeLongList = SqlDb.GetListBySql<StorageFeeLong>(@$"SELECT [sid],snapshot_date,s_6_mo_long_terms_storage_fee,s_12_mo_long_terms_storage_fee,currency FROM SD_LX_StorageFeeLong  WHERE  snapshot_date>='{dateForm}'");
            List<OL_Rep_StorageFee> dataList = new List<OL_Rep_StorageFee>();
            List<OL_Rep_StorageFee> dataList_end = new List<OL_Rep_StorageFee>();
            //获取需要计费的所有日期
            List<string> dateList = new List<string>();
            var dateForm_ = dateForm.ToDateTime();
            var days = (DateTime.Now.Date - dateForm_).Days + 1;
            for (int i = 0; i < days; i++)
            {
                dateList.Add(dateForm_.AddDays(i).ToString("yyyy-MM-dd"));
            }


            //亚马逊月仓费
            foreach (var item1 in storageFeeMonthList)
            {
                OL_Rep_StorageFee row = new OL_Rep_StorageFee();
                var month_rate = (item1.month_of_charge + "-1").ToDateTime().AddMonths(1).ToString("yyyy-MM");//汇率的月份
                var month = (item1.month_of_charge + "-1").ToDateTime().ToString("yyyy-MM");//当前行的月份
                row.AmountOfSource = item1.estimated_monthly_storage_fee;
                row.CurrencyOfSource = item1.currency.ToUpper();
                row.Date = item1.month_of_charge;
                row.Type = "月仓储费";
                row.Warehouse = "亚马逊";
                //获取美元金额,如果不是美元需要转换
                if (item1.currency.ToUpper() != "USD")
                {

                    var rate = rateList.Where(x => x.货币代码.ToUpper() == item1.currency.ToUpper() && x.生效时间.ToDateTime().ToString("yyyy-MM") == month_rate);//当前行的汇率
                    var rate_dollar = rateList.Where(x => x.货币代码 == "USD" && x.生效时间.ToDateTime().ToString("yyyy-MM") == month_rate);//当前行美元汇率
                    if (!rate.IsNullOrEmpty() && rate.Count() > 0 && !rate_dollar.IsNullOrEmpty() && rate_dollar.Count() > 0)
                    {
                        //先转成人民币
                        var rmb = item1.estimated_monthly_storage_fee.ToDouble() * rate.First().汇率.ToDouble();
                        //转成美元
                        row.AmountOfDollar = (rmb * rate_dollar.First().间接汇率.ToDouble()).ToString();
                    }
                }
                else
                {
                    row.AmountOfDollar = row.AmountOfSource;
                }
                //通过店铺获取国家
                var shop = shopList.Where(x => x.ID == item1.sid);
                if (!shop.IsNullOrEmpty() && shop.Count() > 0)
                {
                    row.Country = shop.First().Country;
                }

                dataList.Add(row);
            }
            //亚马逊长期仓储费
            foreach (var item1 in storageFeeLongList)
            {
                OL_Rep_StorageFee row = new OL_Rep_StorageFee();
                var month_rate = item1.snapshot_date.ToDateTime().AddMonths(1).ToString("yyyy-MM");//汇率的月份
                var month = item1.snapshot_date.ToDateTime().ToString("yyyy-MM");//当前行的月份
                var sum = item1.s_6_mo_long_terms_storage_fee.ToDouble() + item1.s_12_mo_long_terms_storage_fee.ToDouble();
                row.AmountOfSource = sum.ToString();
                row.CurrencyOfSource = item1.currency.ToUpper();
                row.Date = month;
                row.Type = "长期仓储费";
                row.Warehouse = "亚马逊";
                //获取美元金额
                if (item1.currency.ToUpper() != "USD")
                {

                    var rate = rateList.Where(x => x.货币代码.ToUpper() == item1.currency.ToUpper() && x.生效时间.ToDateTime().ToString("yyyy-MM") == month_rate);//当前行的汇率
                    var rate_dollar = rateList.Where(x => x.货币代码 == "USD" && x.生效时间.ToDateTime().ToString("yyyy-MM") == month_rate);//当前行美元汇率
                    if (!rate.IsNullOrEmpty() && rate.Count() > 0 && !rate_dollar.IsNullOrEmpty() && rate_dollar.Count() > 0)
                    {
                        //先转成人民币
                        var rmb = sum * rate.First().汇率.ToDouble();
                        //转成美元
                        row.AmountOfDollar = (rmb * rate_dollar.First().间接汇率.ToDouble()).ToString();
                    }
                }
                else
                {
                    row.AmountOfDollar = row.AmountOfSource;
                }



                //通过店铺获取国家
                var shop = shopList.Where(x => x.ID == item1.sid);
                if (!shop.IsNullOrEmpty() && shop.Count() > 0)
                {
                    row.Country = shop.First().Country;
                }

                dataList.Add(row);
            }


            //按照规则分组并均摊一个月内每天的金额
            var group = dataList.GroupBy(x => x.Warehouse + x.Type + x.Date + x.Country);

            foreach (var item1 in group)
            {
                var first = item1.First();
                //获取总的美元金额
                var sumOfDollar = item1.Sum(x => x.AmountOfDollar.ToDouble());
                //获取总的原币金额
                var sumOfSource = item1.Sum(x => x.AmountOfSource.ToDouble());
                //获取一个月的所有日期
                var dates = dateList.Where(x => x.ToDateTime().ToString("yyyy-MM") == first.Date);
                //计算美元金额每天的金额
                var dayOfDollar = (sumOfDollar / dates.Count()).ToString("F6");
                //计算原币金额每天的金额
                var dayOfSource = (sumOfSource / dates.Count()).ToString("F6");
                foreach (var item2 in dates)
                {
                    var row = first.DeepClone();
                    row.AmountOfDollar = dayOfDollar;
                    row.AmountOfSource = dayOfSource;
                    row.Date = item2;
                    row.ID = row.Date + row.Warehouse + row.Type + row.Country;
                    dataList_end.Add(row);

                }
            }
            return dataList_end;
        }




        public List<OL_Rep_StorageFee> GetJFData(string dateForm, List<View_Rate> rateList, List<OL_Shop> shopList)
        {
            
            //数据库连接
            this.SqlDb._conStrings[0] = JsonSettingHelper.GetSetting("Database:BaseDb:ConnectionString");
            //获取九方月仓费
            List<JF_FeeItem> costWaterList = this.SqlDb.GetListBySql<JF_FeeItem>(@$"SELECT * FROM SD_JF_FeeItem WHERE transTime >='{dateForm}' AND feeItemCodeName='仓租(Storage fee)' OR feeItemCodeName='仓租费'");
            List<OL_Rep_StorageFee> dataList = new List<OL_Rep_StorageFee>();
            List<OL_Rep_StorageFee> dataList_end = new List<OL_Rep_StorageFee>();
            foreach (var item1 in costWaterList)
            {
                var month_rate = item1.transTime.ToDateTime().ToString("yyyy-MM");//汇率的月份
                OL_Rep_StorageFee row = new OL_Rep_StorageFee();
                row.Date= item1.transTime;
                row.AmountOfSource= item1.feeAmount.ToString();
                row.CurrencyOfSource= item1.currencyCode;

                //获取美元金额
                if (item1.currencyCode.ToUpper() != "USD")
                {

                    var rate = rateList.Where(x => x.货币代码.ToUpper() == item1.currencyCode.ToUpper() && x.生效时间.ToDateTime().ToString("yyyy-MM") == month_rate);//当前行的汇率
                    var rate_dollar = rateList.Where(x => x.货币代码 == "USD" && x.生效时间.ToDateTime().ToString("yyyy-MM") == month_rate);//当前行美元汇率
                    if (!rate.IsNullOrEmpty() && rate.Count() > 0 && !rate_dollar.IsNullOrEmpty() && rate_dollar.Count() > 0)
                    {
                        //先转成人民币
                        var rmb = item1.feeAmount.ToString().ToDouble() * rate.First().汇率.ToDouble();
                        //转成美元
                        row.AmountOfDollar = (rmb * rate_dollar.First().间接汇率.ToDouble()).ToString();
                    }
                    row.Country = "其他";
                }
                else
                {
                    row.Country = "美国";
                    row.AmountOfDollar = row.AmountOfSource;
                }
                row.Type="日仓储费";
                row.Warehouse = "九方";
                dataList.Add(row);
            }
            //分组
            var group = dataList.GroupBy(x => x.Warehouse + x.Type + x.Date.ToDateTime().ToString("yyyy-MM-dd") + x.Country + x.CurrencyOfSource);
            foreach (var item1 in group)
            {
                var row = item1.First().DeepClone();
                //获取总的美元金额
                row.AmountOfDollar = item1.Sum(x => x.AmountOfDollar.ToDouble()).ToString();
                //获取总的原币金额
                row.AmountOfSource = item1.Sum(x => x.AmountOfSource.ToDouble()).ToString();
                row.ID = row.Warehouse + row.Type + row.Date + row.Country + row.CurrencyOfSource;
                dataList_end.Add(row);
            }


            return dataList_end;
        }



        public List<OL_Rep_StorageFee> GetCLData(string dateForm, List<View_Rate> rateList, List<OL_Shop> shopList)
        {
            OL_CLStorageFeeInterfaceBusiness interfaceBusiness = new OL_CLStorageFeeInterfaceBusiness();
            //获取目标数据
            interfaceBusiness.Filters.Clear();
            interfaceBusiness.Filters.Add(
            //13:>,14>=15:<,16<= 
            new OL_FilterDTO()
            {
                ControlId = "6876fd23a31d84c7f6686ac5",
                DataType = 15,
                SpliceType = 1,
                FilterType = 14,
                Value = dateForm
            });
            List<OL_Rep_StorageFee> storageFee = interfaceBusiness.Query<OL_Rep_StorageFee>(interfaceBusiness.queryOption, interfaceBusiness.tableField, interfaceBusiness.SqlDb);
            storageFee=storageFee.Where(x => x.Type.Contains("仓储费")).ToList();




            List<OL_Rep_StorageFee> dataList = new List<OL_Rep_StorageFee>();
            List<OL_Rep_StorageFee> dataList_end = new List<OL_Rep_StorageFee>();
            foreach (var item1 in storageFee)
            {
                var amount = item1.AmountOfSource.ToDouble() * -1;
                var month_rate = item1.Date.ToDateTime().ToString("yyyy-MM");//汇率的月份
                OL_Rep_StorageFee row = new OL_Rep_StorageFee();
                row.Date = item1.Date;
                row.AmountOfSource = amount.ToString();
                row.CurrencyOfSource = item1.CurrencyOfSource;

                //获取美元金额
                if (item1.CurrencyOfSource.ToUpper() != "USD")
                {

                    var rate = rateList.Where(x => x.货币代码.ToUpper() == item1.CurrencyOfSource.ToUpper() && x.生效时间.ToDateTime().ToString("yyyy-MM") == month_rate);//当前行的汇率
                    var rate_dollar = rateList.Where(x => x.货币代码 == "USD" && x.生效时间.ToDateTime().ToString("yyyy-MM") == month_rate);//当前行美元汇率
                    if (!rate.IsNullOrEmpty() && rate.Count() > 0 && !rate_dollar.IsNullOrEmpty() && rate_dollar.Count() > 0)
                    {
                        //先转成人民币
                        var rmb = amount * rate.First().汇率.ToDouble();
                        //转成美元
                        row.AmountOfDollar = (rmb * rate_dollar.First().间接汇率.ToDouble()).ToString();
                    }
                    row.Country = "其他";
                }
                else
                {
                    row.Country = "美国";
                    row.AmountOfDollar = row.AmountOfSource;
                }
                row.Type = "日仓储费";
                row.Warehouse = "橙联";
                dataList.Add(row);
            }
            //分组
            var group = dataList.GroupBy(x => x.Warehouse + x.Type + x.Date.ToDateTime().ToString("yyyy-MM-dd") + x.Country + x.CurrencyOfSource);
            foreach (var item1 in group)
            {
                var row = item1.First().DeepClone();
                //获取总的美元金额
                row.AmountOfDollar = item1.Sum(x => x.AmountOfDollar.ToDouble()).ToString();
                //获取总的原币金额
                row.AmountOfSource = item1.Sum(x => x.AmountOfSource.ToDouble()).ToString();
                row.ID = row.Warehouse + row.Type + row.Date + row.Country + row.CurrencyOfSource;
                dataList_end.Add(row);
            }


            return dataList_end;
        }


        public List<OL_Rep_StorageFee> GetZBData(string dateForm, List<View_Rate> rateList, List<OL_Shop> shopList)
        {
            OL_StorageFeeInterfaceBusiness interfaceBusiness = new OL_StorageFeeInterfaceBusiness();
            //获取目标数据
            interfaceBusiness.Filters.Clear();
            interfaceBusiness.Filters.Add(
            //13:>,14>=15:<,16<= 
            new OL_FilterDTO()
            {
                ControlId = "68771a33a31d84c7f66bd2bf",
                DataType = 15,
                SpliceType = 1,
                FilterType = 14,
                Value = dateForm
            });
            interfaceBusiness.Filters.Add(
            //13:>,14>=15:<,16<= 
            new OL_FilterDTO()
            {
                ControlId = "6880783fa31d84c7f6817cac",
                DataType = 11,
                SpliceType = 1,
                FilterType = 2,
                Value = "众包"
            });
            List<OL_Rep_StorageFee> storageFee = interfaceBusiness.Query<OL_Rep_StorageFee>(interfaceBusiness.queryOption, interfaceBusiness.tableField, interfaceBusiness.SqlDb);
            List<OL_Rep_StorageFee> dataList = new List<OL_Rep_StorageFee>();
            List<OL_Rep_StorageFee> dataList_end = new List<OL_Rep_StorageFee>();
            foreach (var item1 in storageFee)
            {
                var month_rate = item1.Date.ToDateTime().ToString("yyyy-MM");//汇率的月份
                OL_Rep_StorageFee row = new OL_Rep_StorageFee();
                row.Date = item1.Date;
                row.AmountOfSource = item1.AmountOfSource;
                row.CurrencyOfSource = item1.CurrencyOfSource;

                //获取美元金额
                if (item1.CurrencyOfSource.ToUpper() != "USD")
                {

                    var rate = rateList.Where(x => x.货币代码.ToUpper() == item1.CurrencyOfSource.ToUpper() && x.生效时间.ToDateTime().ToString("yyyy-MM") == month_rate);//当前行的汇率
                    var rate_dollar = rateList.Where(x => x.货币代码 == "USD" && x.生效时间.ToDateTime().ToString("yyyy-MM") == month_rate);//当前行美元汇率
                    if (!rate.IsNullOrEmpty() && rate.Count() > 0 && !rate_dollar.IsNullOrEmpty() && rate_dollar.Count() > 0)
                    {
                        //先转成人民币
                        var rmb = item1.AmountOfSource.ToDouble() * rate.First().汇率.ToDouble();
                        //转成美元
                        row.AmountOfDollar = (rmb * rate_dollar.First().间接汇率.ToDouble()).ToString();
                    }
                    row.Country = "其他";
                }
                else
                {
                    row.Country = "美国";
                    row.AmountOfDollar = row.AmountOfSource;
                }
                row.Type = "日仓储费";
                row.Warehouse = "众包";
                dataList.Add(row);
            }
            //分组
            var group = dataList.GroupBy(x => x.Warehouse + x.Type + x.Date.ToDateTime().ToString("yyyy-MM-dd") + x.Country + x.CurrencyOfSource);
            foreach (var item1 in group)
            {
                var row = item1.First().DeepClone();
                //获取总的美元金额
                row.AmountOfDollar = item1.Sum(x => x.AmountOfDollar.ToDouble()).ToString();
                //获取总的原币金额
                row.AmountOfSource = item1.Sum(x => x.AmountOfSource.ToDouble()).ToString();
                row.ID = row.Warehouse + row.Type + row.Date + row.Country + row.CurrencyOfSource;
                dataList_end.Add(row);
            }


            return dataList_end;
        }


        public List<OL_Rep_StorageFee> GetBGAData(string dateForm, List<View_Rate> rateList, List<OL_Shop> shopList)
        {
            OL_StorageFeeInterfaceBusiness interfaceBusiness = new OL_StorageFeeInterfaceBusiness();
            //获取目标数据
            interfaceBusiness.Filters.Clear();
            interfaceBusiness.Filters.Add(
            //13:>,14>=15:<,16<= 
            new OL_FilterDTO()
            {
                ControlId = "68771a33a31d84c7f66bd2bf",
                DataType = 15,
                SpliceType = 1,
                FilterType = 14,
                Value = dateForm
            });
            interfaceBusiness.Filters.Add(
            //13:>,14>=15:<,16<= 
            new OL_FilterDTO()
            {
                ControlId = "6880783fa31d84c7f6817cac",
                DataType = 11,
                SpliceType = 1,
                FilterType = 2,
                Value = "BGA"
            });
            List<OL_Rep_StorageFee> storageFee = interfaceBusiness.Query<OL_Rep_StorageFee>(interfaceBusiness.queryOption, interfaceBusiness.tableField, interfaceBusiness.SqlDb);

            List<OL_Rep_StorageFee> dataList = new List<OL_Rep_StorageFee>();
            List<OL_Rep_StorageFee> dataList_end = new List<OL_Rep_StorageFee>();
            foreach (var item1 in storageFee)
            {
                var month_rate = item1.Date.ToDateTime().ToString("yyyy-MM");//汇率的月份
                OL_Rep_StorageFee row = new OL_Rep_StorageFee();
                row.Date = item1.Date;
                row.AmountOfSource = item1.AmountOfSource;
                row.CurrencyOfSource = item1.CurrencyOfSource;

                //获取美元金额
                if (item1.CurrencyOfSource.ToUpper() != "USD")
                {

                    var rate = rateList.Where(x => x.货币代码.ToUpper() == item1.CurrencyOfSource.ToUpper() && x.生效时间.ToDateTime().ToString("yyyy-MM") == month_rate);//当前行的汇率
                    var rate_dollar = rateList.Where(x => x.货币代码 == "USD" && x.生效时间.ToDateTime().ToString("yyyy-MM") == month_rate);//当前行美元汇率
                    if (!rate.IsNullOrEmpty() && rate.Count() > 0 && !rate_dollar.IsNullOrEmpty() && rate_dollar.Count() > 0)
                    {
                        //先转成人民币
                        var rmb = item1.AmountOfSource.ToDouble() * rate.First().汇率.ToDouble();
                        //转成美元
                        row.AmountOfDollar = (rmb * rate_dollar.First().间接汇率.ToDouble()).ToString();
                    }
                    row.Country = "其他";
                }
                else
                {
                    row.Country = "美国";
                    row.AmountOfDollar = row.AmountOfSource;
                }
                row.Type = "日仓储费";
                row.Warehouse = "BGA";
                dataList.Add(row);
            }
            //分组
            var group = dataList.GroupBy(x => x.Warehouse + x.Type + x.Date.ToDateTime().ToString("yyyy-MM-dd") + x.Country + x.CurrencyOfSource);
            foreach (var item1 in group)
            {
                var row = item1.First().DeepClone();
                //获取总的美元金额
                row.AmountOfDollar = item1.Sum(x => x.AmountOfDollar.ToDouble()).ToString();
                //获取总的原币金额
                row.AmountOfSource = item1.Sum(x => x.AmountOfSource.ToDouble()).ToString();
                row.ID = row.Warehouse + row.Type + row.Date + row.Country + row.CurrencyOfSource;
                dataList_end.Add(row);
            }
            return dataList_end;
        }





        /// <summary>
        /// 任务
        /// </summary>
        public void Job()
        {
            ExecuteInterface();
        }
    }

}
